<?php
/**
 * Marco Mounzer
 * m.mounzer@gmail.com
 * @copyright 2013
 */
class Database {

    /*private $user = "automoda";
    private $pass = "lcz193qo";
    private $host = "localhost";
    private $database = "automoda_info";*/
    
    private $user = "root";
    private $pass = "root";
    private $host = "localhost";
    private $database = "test3";
    private $dbh = null;
    private static $db = null; //class db

    private function __construct() {
        
    }

    public static function GetIstance() {
        if (is_null(self::$db)) {
            self::$db = new Database();
        }
        return self::$db;
    }

    public function connect() {
        try {
            if (!is_resource($this->dbh)) {
                $this->dbh = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->database, $this->user, $this->pass, array(PDO::ATTR_PERSISTENT => false));
            }
        } catch (PDOException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function disconnect() {
        if (is_resource($this->dbh))
            $this->dbh = null;
    }

    public function quote($text) {
        //if (is_resource($this->dbh))
        return $this->dbh->quote($text);

        throw new Exception('Nessuna connessione');
    }

    private function startTransaction() {
        $this->dbh->beginTransaction();
    }

    private function CommitTransaction() {
        $this->dbh->commit();
    }

    private function RollbackTransaction() {
        $this->dbh->rollBack();
    }

    /**
     *
     * @param string $sql
     * @param bool $array if false, return stdClass array
     * @return array 
     */
    public function select($sql, $array = false) {
        $res = array();
        $result = $this->dbh->query($sql);

        foreach ($result as $r) {
            if (!$array) {
                $row = new stdClass();
                foreach ($r as $k => $v) {
                    $row->$k = $v;
                }
                $res[] = $row;
            }
            else
                $res[] = $r;
        }
        return $res;
    }

    /**
     *  @table = nome tabella
     *  @$dati = Array("id"=>1, "nome"=>"insert", ..);
     *  restituisce l'id inserito
     * */
    public function insert($table, $dati) {
        $fields = Array();
        $records = Array();
        foreach ($dati as $key => $record) {
            $fields[] = $key;
            $records[] = $record;
        }

        try {
            $sql = "INSERT INTO $table (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $this->add2pInsert($fields)) . ");";
            $stmt = $this->dbh->prepare($sql);

            for ($i = 0; $i < count($fields); $i++) {
                $stmt->bindParam(":" . $fields[$i], $records[$i]);
            }
            $stmt->execute();

            $err = $stmt->errorInfo();

            if (is_array($err)) {
                if ($err[0] != '00000')
                    throw new PDOException($err[2], $err[0]);
            }
        } catch (PDOException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
        return $this->dbh->lastInsertId();
        //return $this->lastInsertId($table);
    }

    private function lastInsertId($table) {
        $sql = "SELECT id FROM $table WHERE oid = " . $this->dbh->lastInsertId();
        die("<pre>" . print_r($sql, 1) . "</pre>");
        $res = $this->select($sql);
        return $res[0]->id;
    }

    /**
     *  @table = nome tabella
     *  @$dati = Array("id"=>1, "nome"=>"insert", ..);
     *  @$where = " a = b and c < d or ecc ecc"
     * */
    public function update($table, $dati, $where = null) {
        $fields = Array();
        $records = Array();

        foreach ($dati as $key => $record) {
            $fields[] = $key;
            $records[] = $record;
        }

        try {
            $sql = "UPDATE $table SET " . implode(", ", $this->add2pUpdate($fields));

            if (strlen($where) > 0) {
                $sql .= " WHERE " . $where;
            }

            $stmt = $this->dbh->prepare($sql);
            for ($i = 0; $i < count($fields); $i++) {
                $stmt->bindParam(":" . $fields[$i], $records[$i]);
            }
            $res = $stmt->execute();

            $err = $stmt->errorInfo();

            if (is_array($err)) {
                if ($err[0] != '00000')
                    throw new PDOException($err[2], $err[1]);
            }
            return $res;
        } catch (PDOException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    /**
     * genera i nomi delle variabili utilizzate come parametri per l'sql insert
     * */
    private function add2pInsert($arr) {
        $arr_app = Array();

        foreach ($arr as $v) {
            $arr_app[] = ":" . $v;
        }
        return $arr_app;
    }

    /**
     * genera i nomi delle variabili utilizzate come parametri per l'sql update e where
     * */
    private function add2pUpdate($arr) {
        $arr_app = Array();

        foreach ($arr as $v) {
            $arr_app[] = "$v = :" . $v;
        }
        return $arr_app;
    }

    /**
     *  @table = nome tabella
     *  @$where = sql standard
     *  restituisce true o false
     * */
    public function delete($table, $where) {
        try {
            $sql = "DELETE FROM $table";

            if (strlen($where) > 0) {
                $sql .= " WHERE " . $where;
            }

            $stmt = $this->dbh->prepare($sql);

            $res = $stmt->execute();

            $err = $stmt->errorInfo();

            if (is_array($err)) {
                if ($err[0] != '00000')
                    throw new PDOException($err[2], $err[0]);
            }
            return $res;
        } catch (PDOException $e) {
            throw $e;
        }
    }

    /**
     * 
     * @param type $class
     * @param type $id
     * @return boolean
     * @throws Exception
     */
    public function loadObject($class, $id) {

        $table = self::getTableNameFromClassName($class);

        $sql = "SELECT * FROM $table WHERE id = " . $this->quote($id);

        $res = $this->select($sql);

        if (count($res) != 1)
            return false;

        $record = $res[0];

        $reflect = new ReflectionClass($class);
        $propsClass = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);

        foreach ($propsClass as $prop) {
            if (is_object($class->{$prop->getName()})) {
                if (in_array($record->{strtolower($prop->getName()) . '_id'}, get_object_vars($record))) {
                    //$class->{$prop->getName()}->Id = $record->{strtolower($prop->getName()) . '_id'};

                    $obj = Database::GetIstance()->loadObject($class->{$prop->getName()}, $record->{strtolower($prop->getName()) . '_id'});
                    if ($obj)
                        $class->{$prop->getName()} = $obj;
                    else
                        $class->{$prop->getName()} = new $class->{$prop->getName()};
                } else
                    throw new Exception('Missing parameter: ' . strtolower($prop->getName()) . '_id');
            } else {
                if (strtolower($prop->getName()) == 'id') {
                    $class->{$prop->getName()} = intval($record->{strtolower($prop->getName())}) > 0 ? intval($record->{strtolower($prop->getName())}) : 0;
                    continue;
                }

                if (in_array($record->{strtolower($prop->getName())}, get_object_vars($record)))
                    $class->{$prop->getName()} = $record->{strtolower($prop->getName())};
                else
                    throw new Exception('Missing parameter: ' . strtolower($prop->getName()));
            }
        }
        return $class;
    }

    public function loadObjects($class, $where = null, $order_by = null) {
        $table = self::getTableNameFromClassName($class);

        $sql = "SELECT * FROM $table";
        if (!is_null($where))
            $sql .= ' WHERE ' . $where;
        if (!is_null($order_by))
            $sql .= ' ORDER BY ' . $order_by;

        $res = $this->select($sql);

        if (count($res) == 0)
            return array();
        $reflect = new ReflectionClass($class);
        $propsClass = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);

        $data = array();
        foreach ($res as $record) {
            $c = new $class;
            foreach ($propsClass as $prop) {
                if (is_object($c->{$prop->getName()})) {

                    if (in_array($record->{strtolower($prop->getName()) . '_id'}, get_object_vars($record))) {

                        $obj = Database::GetIstance()->loadObject($c->{$prop->getName()}, $record->{strtolower($prop->getName()) . '_id'});
                        if ($obj)
                            $c->{$prop->getName()} = $obj;
                        else
                            $c->{$prop->getName()} = new $c->{$prop->getName()};

                        //$c->{$prop->getName()}->Id = $record->{strtolower($prop->getName()) . '_id'};
                    } else
                        throw new Exception('Missing parameter: ' . strtolower($prop->getName()) . '_id');
                } else {
                    if (strtolower($prop->getName()) == 'id') {
                        $c->{$prop->getName()} = intval($record->{strtolower($prop->getName())}) > 0 ? intval($record->{strtolower($prop->getName())}) : 0;
                        continue;
                    }

                    if (in_array($record->{strtolower($prop->getName())}, get_object_vars($record)))
                        $c->{$prop->getName()} = $record->{strtolower($prop->getName())};
                    else
                        throw new Exception('Missing parameter: ' . strtolower($prop->getName()));
                }
            }
            $data[] = $c;
        }
        return $data;
    }

    public static function CamelCaseToUnderscore($string) {
        return preg_replace(array('#(?<=(?:\p{Lu}))(\p{Lu}\p{Ll})#', '#(?<=(?:\p{Ll}|\p{Nd}))(\p{Lu})#'), array('_\1', '_\1'), $string);
    }

    public static function getTableNameFromClassName($class) {
        $s = self::CamelCaseToUnderscore(get_class($class));
        $table = explode('_', $s);
        $schema = array_shift($table);
        //return strtolower($schema . '.' . implode('_', $table));
        return strtolower(implode('_', $table));
    }

    private static function _getParam($class) {
        $reflect = new ReflectionClass($class);
        $props = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);

        $param = array();
        foreach ($props as $prop) {
            //$param[strtolower($prop->getName())] = $class->{$prop->getName()};
            if (is_object($class->{$prop->getName()})) {
                $param[strtolower($prop->getName()) . '_id'] = $class->{$prop->getName()}->Id;
            } else {
                $param[strtolower($prop->getName())] = $class->{$prop->getName()};
            }
        }
        return $param;
    }

    public static function SaveOrUpdateObject($obj) {
        $data = self::_getParam($obj);

        $table = self::getTableNameFromClassName($obj);

        if ($obj->Id == 0) {
            unset($data['id']);
            $id = self::GetIstance()->insert($table, $data);
        } else {
            self::GetIstance()->update($table, $data, 'id = ' . self::GetIstance()->quote($obj->Id));
            $id = $obj->Id;
        }

        return self::GetIstance()->loadObject($obj, $id);
    }

    public static function DeleteObject($obj) {
        $table = self::getTableNameFromClassName($obj);

        return self::GetIstance()->delete($table, 'id=' . self::GetIstance()->quote($obj->Id));
    }

}